package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.druid.pool.DruidPooledPreparedStatement.PreparedStatementKey;

import dao.StuDao;
import entity.Score;
import entity.Stu;
import entity.StuInfo;
import util.DBUtils;

public class StuDaoImpl implements StuDao {

	@Override
	public Stu selectById(int stuId) {
		try (Connection c = DBUtils.getConnection()) {
			String sql = "select stu_id,stu_name,stu_class,info_id,stu_sex,stu_age,stu_address"
					+ " from stu_tb left join stu_info using(stu_id) "
					+ " where stu_id=? and is_delete=false";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, stuId);
			ResultSet rs = ps.executeQuery();
			
			// 结果集必须next()才能向下拿一行
			if (rs.next()) {
				Stu stu = new Stu();
				stu.setStuId(rs.getInt(1));
				stu.setStuName(rs.getString(2));
				stu.setStuClass(rs.getString(3));
				
				StuInfo info = new StuInfo();
				info.setInfoId(rs.getInt(4));
				info.setStuSex(rs.getString(5));
				info.setStuAge(rs.getInt(6));
				info.setStuAddress(rs.getString(7));
				
				stu.setInfo(info);
				return stu;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Stu selectById2(int stuId) {
		try (Connection c = DBUtils.getConnection()) {
			String sql = "select stu_id,stu_name,stu_class,score_id,course_name,score"
					+ " from stu_tb left join score_tb using(stu_id)" 
					+ " where stu_id = ? and is_delete=false";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, stuId);
			ResultSet rs = ps.executeQuery();
			
			// 结果集必须next()才能向下拿一行
			List<Score> scores = new ArrayList<>();
			if (rs.next()) {
				Stu stu = new Stu();
				stu.setStuId(rs.getInt(1));
				stu.setStuName(rs.getString(2));
				stu.setStuClass(rs.getString(3));
				
				do {
					Score s = new Score();
					s.setScoreId(rs.getInt(4));
					s.setCourseName(rs.getString(5));
					s.setScore(rs.getInt(6));
					scores.add(s);
				} while(rs.next());
				
				stu.setScores(scores);
				return stu;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public boolean insert(Stu stu) {
		try (Connection c = DBUtils.getConnection()){
			String sql = "insert into stu_tb(stu_name,stu_class,create_time) values(?,?,now())";
			// 第二个参数是，执行完sql返回新增的主键
			PreparedStatement ps = c.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			
			ps.setString(1, stu.getStuName());
			ps.setString(2, stu.getStuClass());
			
			int row = ps.executeUpdate();
			// 执行后获取主键集合
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				// 生成的主键只有一列
				System.out.println("生成的主键:" + rs.getInt(1));
			}
			
			return 1 == row ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean delete(int stuId) {
		try (Connection c = DBUtils.getConnection()){
			// 更新删除标记，逻辑删除，并且更新更改时间
			String sql = "update stu_tb set is_delete=true,modify_time=now() "
					+ " where stu_id=? and is_delete=false";
			// 第二个参数是，执行完sql返回新增的主键
			PreparedStatement ps = c.prepareStatement(sql);
			
			ps.setInt(1, stuId);
			
			int row = ps.executeUpdate();
			
			return 1 == row ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

}
